<?php
/**
 * MYSQL类
 * 
 * Copyright (c) 2014~2019 https://desion.net All rights reserved.
 * Author: 阿林 QQ:120867629 <linhong00@gmail.com>
 */
declare (strict_types = 1);
namespace dsphp\db;
//use PDO;
use dsphp\db\common\PDOConnection;

class Mysql extends PDOConnection
{
    // 数据库连接参数配置
    protected $config = [
        // 数据库类型
        'type'              => 'mysql',
        // 服务器地址
        'hostname'          => '127.0.0.1',
        // 数据库名
        'database'          => '',
        // 用户名
        'username'          => '',
        // 密码
        'password'          => '',
        // 端口
        'hostport'          => '',
        // 连接dsn
        'dsn'               => '',
        // 数据库连接参数
        'params'            => [],
        // 数据库编码默认采用utf8
        'charset'           => 'utf8',
        // 数据库表前缀
        'prefix'            => '',
        // 数据库调试模式
        'debug'             => false,
        // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
        'deploy'            => 0,
        // 数据库读写是否分离 主从式有效
        'rw_separate'       => false,
        // 读写分离后 主服务器数量
        'master_num'        => 1,
        // 指定从服务器序号
        'slave_no'          => '',
        // 模型写入后自动读取主服务器
        'read_master'       => false,
        // 是否严格检查字段是否存在
        'fields_strict'     => true,
        // Builder类
        'builder'           => '',
        // Query类
        'query'             => '',
        // 是否需要断线重连
        'break_reconnect'   => false,
        // 断线标识字符串
        'break_match_str'   => [],
        // 字段缓存路径
        'schema_cache_path' => '',
    ];

    // 操作信息
    protected $options = [
        'field'     => [],
        'table'     => '',
        'alias'     => '',
        'partition' => '',
        'join'      => [],
        'group'     => [],
        'having'    => '',
        'limit'     => '',
        'order'     => [],
        'force'     => [],
        'comment'   => '',
        'where'     => [],
        'lock'      => false,
        'procedure' => false,
        'master'    => false,
        'replace'   => false,
        'duplicate' => [],
        'data'      => []
    ];

    // SELECT SQL表达式
    protected $selectSql = 'SELECT %FIELD% FROM %TABLE%%PARTITION%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT% %LOCK%%COMMENT%';
    // INSERT SQL表达式
    protected $insertSql = '%INSERT% INTO %TABLE%%PARTITION% SET %SET% %DUPLICATE%%COMMENT%';
    // INSERT ALL SQL表达式
    protected $insertAllSql = '%INSERT% INTO %TABLE%%PARTITION% (%FIELD%) VALUES %DATA% %DUPLICATE%%COMMENT%';
    // UPDATE SQL表达式
    protected $updateSql = 'UPDATE %TABLE%%PARTITION% %JOIN% SET %SET% %WHERE% %ORDER%%LIMIT% %LOCK%%COMMENT%';
    // DELETE SQL表达式
    protected $deleteSql = 'DELETE FROM %TABLE%%PARTITION%%USING%%JOIN%%WHERE%%ORDER%%LIMIT% %LOCK%%COMMENT%';

    /**
     * 解析pdo连接的dsn信息
     *
     * @param  array $config 连接信息
     */
    protected function parseDsn(array $config): string
    {
        if (! empty($config['socket'])) {
            $dsn = 'mysql:unix_socket=' . $config['socket'];
        } elseif (! empty($config['hostport'])) {
            $dsn = 'mysql:host=' . $config['hostname'] . ';port=' . $config['hostport'];
        } else {
            $dsn = 'mysql:host=' . $config['hostname'];
        }
        $dsn .= ';dbname=' . $config['database'];

        if (!empty($config['charset'])) {
            $dsn .= ';charset=' . $config['charset'];
        }

        return $dsn;
    }

    /**
     * 设置操作信息
     *
     * @param  mixed  $val   信息
     * @return this
     */
    public function setOptions(array $val)
    {
        $this->options = $val;
        return $this;
    }

    /**
     * 生成查询SQL
     *
     * @param  bool   $one    是否仅获取一个记录
     * @return mixed
     */
    public function select(bool $isSql = false)
    {
        $options = $this->options;

        $sql = str_replace(
            ['%TABLE%', '%PARTITION%', '%FIELD%', '%JOIN%', '%WHERE%', '%GROUP%', '%HAVING%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%', '%FORCE%'],
            [
                $this->parseTable($options['table'], $options['alias']),
                $this->parsePartition($options['partition']),
                $this->parseField($options['field']),
                $this->parseJoin($options['join']),
                $this->parseWhere($options['where']),
                $this->parseGroup($options['group']),
                $this->parseHaving($options['having']),
                $this->parseOrder($options['order']),
                $this->parseLimit($options['limit']),
                $this->parseLock($options['lock']),
                $this->parseComment($options['comment']),
                $this->parseForce($options['force']),
            ],
            $this->selectSql);

        if ($isSql) {
            return $sql;
        }

        return $this->query($sql);
    }

    /**
     * 插入记录
     *
     * @param boolean   $isSql 返回SQL
     * @return mixed
     */
    public function insert(bool $isSql = false)
    {
        $options = $this->options;
        // 分析并处理数据
        $data = $this->parseData($options['data']);
        if (empty($data)) {
            return $isSql ? '' : 0;
        }

        $set = [];
        foreach ($data as $key => $val) {
            $set[] = $key . ' = ' . $val;
        }

        $sql = str_replace(
            ['%INSERT%', '%TABLE%', '%PARTITION%', '%SET%', '%DUPLICATE%', '%COMMENT%'],
            [
                $options['replace'] ? 'REPLACE' : 'INSERT',
                $this->parseTable($options['table'], ''),
                $this->parsePartition($options['partition']),
                implode(' , ', $set),
                $this->parseDuplicate($options['duplicate'], $data), // 更新唯一
                $this->parseComment($options['comment']),
            ],
            $this->insertSql);

        if ($isSql) {
            return $sql;
        }
        // 执行操作
        $result    = $this->execute($sql);
        $lastInsId = 0;

        if ($result) {
            $lastInsId = $this->getLastInsID($options['pk']);
        }

        return (int)$lastInsId;
    }

    /**
     * 批量插入记录
     *
     * @param integer   $limit   每次写入数据限制
     * @return integer
     */
    public function insertAll(int $limit = 0): int
    {
        $options = $this->options;
        $dataSet = $options['data'];

        if (! is_array(reset($dataSet))) {
            return 0;
        }

        if ($limit == 0) {
            $sql = $this->insertAllSql($dataSet, $options);

            return $this->execute($sql);
        }

        // 分批写入 自动启动事务支持
        $this->startTrans();

        try {
            $array = array_chunk($dataSet, $limit, true);
            $count = 0;

            foreach ($array as $item) {
                $sql = $this->insertAllSql($item, $options);
                $count += $this->execute($sql);
            }

            // 提交事务
            $this->commit();
        } catch (\Exception | \Throwable $e) {
            $this->rollback();
            throw $e;
        }

        return $count;
    }

    /**
     * 生成insertall SQL
     *
     * @param  array     $dataSet 数据集
     * @param  bool      $replace 是否replace
     * @return string
     */
    public function insertAllSql(array $dataSet, array $options): string
    {
        $fields = [];
        $values = [];

        foreach ($dataSet as $data) {
            $data = $this->parseData($data);

            $values[] = '( ' . implode(',', array_values($data)) . ' )';

            if (!isset($insertFields)) {
                $insertFields = array_keys($data);
            }
        }

        foreach ($insertFields as $field) {
            $fields[] = $this->parseKey($field);
        }

        return str_replace(
            ['%INSERT%', '%TABLE%', '%PARTITION%', '%FIELD%', '%DATA%', '%DUPLICATE%', '%COMMENT%'],
            [
                $options['replace'] ? 'REPLACE' : 'INSERT',
                $this->parseTable($options['table'], ''),
                $this->parsePartition($options['partition']),
                implode(' , ', $fields),
                implode(' , ', $values),
                $this->parseDuplicate($options['duplicate'], $data), // 更新唯一
                $this->parseComment($options['comment']),
            ],
            $this->insertAllSql);
    }

    /**
     * 更新记录
     */
    public function update(bool $isSql = false)
    {
        $options = $this->options;
        $data    = $this->parseData($options['data']);

        if (empty($data)) {
            return $isSql ? '' : 0;
        }

        $set = [];
        foreach ($data as $key => $val) {
            $set[] = $key . ' = ' . $val;
        }

        $sql = str_replace(
            ['%TABLE%', '%PARTITION%', '%SET%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($options['table'], ''),
                $this->parsePartition($options['partition']),
                implode(' , ', $set),
                $this->parseJoin($options['join']),
                $this->parseWhere($options['where']),
                $this->parseOrder($options['order']),
                $this->parseLimit($options['limit']),
                $this->parseLock($options['lock']),
                $this->parseComment($options['comment']),
            ],
            $this->updateSql);
        
        if ($isSql) {
            return $sql;
        }

        // 执行操作
        $result = $this->execute($sql);

        return $result;
    }

    /**
     * 删除记录
     */
    public function delete(bool $isSql = false)
    {
        $options = $this->options;

        $sql = str_replace(
            ['%TABLE%', '%PARTITION%', '%USING%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($options['table'], ''),
                $this->parsePartition($options['partition']),
                '', //!empty($options['using']) ? ' USING ' . $this->parseTable($options['using']) . ' ' : '',
                $this->parseJoin($options['join']),
                $this->parseWhere($options['where']),
                $this->parseOrder($options['order']),
                $this->parseLimit($options['limit']),
                $this->parseLock($options['lock']),
                $this->parseComment($options['comment']),
            ],
            $this->deleteSql);

        if ($isSql) {
            return $sql;
        }
        // 执行操作
        $result = $this->execute($sql);

        return $result;
    }

    /**
     * 表名分析
     *
     * @param  string  $name   表名
     * @param  string  $alias  别名
     * @return string
     */
    protected function parseTable($name, $alias): string
    {
        if (empty($alias)) {
            return $name;
        }

        return $name . ' ' . $alias;
    }

    /**
     * 分析查询字段
     *
     * @param array $list
     * @return string
     */
    protected function parseField(array $list):string
    {
        if (empty($list)) {
            return '*';
        }

        $fields = [];

        foreach ($list as $alias => $field) {
            if (is_numeric($alias)) {
                $fields[] = $field;
                continue;
            }

            $fields[] = "{$field} AS {$alias}";
        }

        return implode(',', $fields);
    }

    /**
     * Partition 分析
     *
     * @param  string|array $partition  分区
     * @return string
     */
    protected function parsePartition($partition): string
    {
        if ('' == $partition) {
            return '';
        }

        if (is_string($partition)) {
            $partition = explode(',', $partition);
        }

        return ' PARTITION (' . implode(' , ', $partition) . ') ';
    }

    /**
     * join分析
     *
     * @param  array $join
     * @return string
     */
    protected function parseJoin(array $join): string
    {
        $joinStr = '';

        foreach ($join as $item) {
            list($table, $on, $type) = $item;

            if (strpos($on, '=')) {
                list($val1, $val2) = explode('=', $on, 2);

                $condition = $this->parseKey( $val1) . '=' . $this->parseKey($val2);
            } else {
                $condition = $on;
            }

            $joinStr .= ' ' . $type . ' JOIN ' . $table . ' ON ' . $condition;
        }

        return $joinStr;
    }

    /**
     * group分析
     *
     * @param  mixed $group
     * @return string
     */
    protected function parseGroup(array $group): string
    {
        if (empty($group)) {
            return '';
        }

        $val = [];
        foreach ($group as $key) {
            $val[] = $this->parseKey($key);
        }

        return ' GROUP BY ' . implode(',', $val);
    }

    /**
     * having分析
     *
     * @param  string $having
     * @return string
     */
    protected function parseHaving(string $having): string
    {
        return $having ? ' HAVING ' . $having : '';
    }


    /**
     * limit分析
     *
     * @param  mixed $limit
     * @return string
     */
    protected function parseLimit(string $limit): string
    {
        return $limit ? ' LIMIT ' . $limit . ' ' : '';
    }

    /**
     * order分析
     *
     * @param  array $order
     * @return string
     */
    protected function parseOrder(array $order): string
    {
        $array = [];
        $order = array_map('strtoupper', $order);

        foreach ($order as $key => $sort) {
            if ('[RAND]' == $key) {
                $array[] = 'rand()';
            } else {
                $sort    = in_array($sort, ['ASC', 'DESC'], true) ? ' ' . $sort : '';
                $array[] = $key . $sort;
            }
        }

        return empty($array) ? '' : ' ORDER BY ' . implode(',', $array);
    }


    /**
     * comment分析
     *
     * @param  string $comment
     * @return string
     */
    protected function parseComment(string $comment): string
    {
        if (false !== strpos($comment, '*/')) {
            $comment = strstr($comment, '*/', true);
        }

        return $comment ? ' /* ' . $comment . ' */' : '';
    }

    /**
     * 设置锁机制
     * 
     * @param  bool|string $lock
     * @return string
     */
    protected function parseLock($lock = false): string
    {
        if (is_bool($lock)) {
            return $lock ? ' FOR UPDATE ' : '';
        }

        if (is_string($lock) && $lock) {
            return ' ' . trim($lock) . ' ';
        } else {
            return '';
        }
    }

    /**
     * index分析，可在操作链中指定需要强制使用的索引
     *
     * @param  mixed $index
     * @return string
     */
    protected function parseForce($index): string
    {
        if (empty($index)) {
            return '';
        }

        if (is_array($index)) {
            $index = join(',', $index);
        }

        return sprintf(" FORCE INDEX ( %s ) ", $index);
    }

    /**
     * ON DUPLICATE KEY UPDATE 分析
     *
     * @param  array  $duplicate
     * @return string
     */
    protected function parseDuplicate(array $duplicate, array $data): string
    {
        if (empty($duplicate)) {
            return '';
        }

        $updates = [];
        foreach ($duplicate as $key => $val) {
            $updates[] = $this->parseKey($key) . " = " . $val ?: $data[$key];
        }

        return ' ON DUPLICATE KEY UPDATE ' . implode(' , ', $updates) . ' ';
    }

    /**
     * 数据分析
     *
     * @param  array     $data      数据
     * @param  array     $fields    字段信息
     * @return array
     */
    protected function parseData(array $data = [], array $fields = []): array
    {
        if (empty($data)) {
            return [];
        }

        $result = [];

        foreach ($data as $key => $val) {
            $item = $this->parseKey($key);

            if (is_array($val) && !empty($val)) {
                if (is_numeric($val[1])) {
                    $val[1] = floatval($val[1]);
                }
                switch (strtoupper($val[0])) {
                    case 'INC':
                        $result[$item] = $item . ' + ' . $val[1];
                        break;
                    case 'DEC':
                        $result[$item] = $item . ' - ' . $val[1];
                        break;
                }
            } elseif (is_scalar($val)) {
                // 过滤非标量数据
                $result[$item] = $val;
            }
        }

        return $result;
    }

    /**
     * where 分析
     *
     * @param  array $where  条件
     * @return string
     */
    protected function parseWhere(array $where):string
    {
        $whereStr = '';
        $map      = [];
        
        if (isset($where['map'])) {
            $map = $where['map'];
            unset($where['map']);
        }
        
        $whereStr = $this->formatWhereStr($where);

        foreach ($map as $key => $val) {
            list($logic, $list) = $val;
            $list  = $this->formatWhereStr($list);

            if ($whereStr) {
                $whereStr .= " {$logic} ( {$list} )";
            } else {
                $whereStr = $list;
            }
        }

        return empty($whereStr) ? '' : ' WHERE ' . $whereStr;
    }

    private function formatWhereStr($list)
    {
        $string = '';

        foreach ($list as $k => $v) {
            list($field, $value, $option, $logic) = $v;
            $option  = $this->_getOption($option);

            if ($string) {
                $string .= " {$logic} ";
            }

            $temp = '';
            switch ($option)
            {
                case 'IN':
                case 'NOT IN':
                    $temp = "`{$field}` {$option} ({$value})";
                    break;
                case 'BETWEEN':
                case 'NOT BETWEEN':
                    $value = explode(',', $value);
                    if (count($value) == 2) {
                        $temp = "`{$field}` {$option} {$value[0]} AND {$value[1]}";
                    }
                    break;
                case 'FIND':
                    $temp = 'FIND_IN_SET(' . $value . ', ' . $field . ')';
                    break;
                default :
                    $temp = "`{$field}` {$option} '{$value}'";
            }

            $string .= $temp;
        }

        return $string;
    }

    private function parseKey($key)
    {
        return $key;
    }

    private function _getOption($op)
    {
        return $op;
    }
}
